﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Calculated Columns Package Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:08/05/2008 01:28:33-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Calculated Columns Package Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 08/05/2008 01:28:33</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>This sample works with the SQL Server 2005 version of the <b>AdventureWorks</b> OLTP database. To install this database, see <a href="http://go.microsoft.com/fwlink/?LinkId=122077" alt="blocked::http://go.microsoft.com/fwlink/?LinkId=122077"><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Sample Databases for Microsoft SQL Server 2008</linkText></a>.</p>
    <p>The Calculated Columns sample is a package that processes archived sales transactions. The package uses an OLE DB source, two Derived Column transformations, a Sort transformation, and an Aggregate transformation. The results are sent to a flat file by using a Flat File destination.</p>
    <p>The Calculated Columns sample package illustrates how to extract data from a table, perform mathematical operations on data, aggregate data values, sort data, and write data to a file. The sample package also uses a package configuration, a variable, and a property expression to dynamically update the location of the file to which the sample writes and copy the file to that location. </p>
    <p>If you run the sample on a non-English version of Windows, you may need to substitute the localized name of the Program Files folder to open or run the sample.</p>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
      Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.<p />
    </td></tr></table><p /></div>
  <h1 class="heading">Requirements</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Running this sample package requires the following:</p>
      <ul xmlns=""><li>
          The sample package and data files that it uses must be installed on the local hard disk drive. <br />
        </li><li>
          You must have installed and have administrative permissions on the <b>AdventureWorks</b> OLTP database. This sample retrieves data from the <b>TransactionHistoryArchive</b> table of the sample <b>AdventureWorks</b> database. By default, this table is installed with the <b>AdventureWorks</b> database.<br />
        </li><li>
          If you intend only to run the sample package from the command line, you must install Integration Services. <br />
        </li><li>
          If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio. <br />
        </li></ul>
      <p xmlns="">For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online. </p>
    </content>
  </div><h1 class="heading">Location of the Sample Package</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If the samples were installed to the default installation location, the Calculated Columns sample package is located in the following folder: </p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\.</p>
      <p xmlns="">The following files are required to run this sample package.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              File
            </th>
            <th>
              Description
            </th>
          </tr><tr>
          <td>
            <p>CalculatedColumns.dtsx</p>
          </td>
          <td>
            <p>The sample package.</p>
          </td>
        </tr><tr>
          <td>
            <p>results.txt</p>
          </td>
          <td>
            <p>Flat file sample data.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Running the Sample</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The package can be run from the command line by using the <b>dtexec</b> utility, or can be run in Business Intelligence Development Studio.</p>
      <p xmlns="">If you are using a non-English version of Windows, you may need to update the <b>ConnectionString</b> property of any file connection managers used in the package to run the sample package successfully. Please verify that the path used in the connection manager is valid on your computer, and if required, modify the path to use the localized name of the Program Files folder.</p>
      <p xmlns="">For this sample, you may have to update "Program Files" in the <b>ConnectionString</b> property for the results.txt connection manager.</p>
      <h4 class="procedureHeading" xmlns="">To run the package by using dtexec</h4><div id="procedureSectionEFBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open a Command Prompt window.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Use the <b>Change Directory</b> command, <b>cd</b>, to change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of <b>dtexec</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Type the following command:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"</pre></td></tr></table></span></div>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Press <b>Enter</b>.</p>
            </content>
          </li></ol></div>
      <p xmlns="">For more information about how to run the package by using the <b>dtexec</b> utility, see the topic, "dtexec Utility", in SQL Server Books Online. </p>
      <h4 class="procedureHeading" xmlns="">To run the package in Business Intelligence Development Studio</h4><div id="procedureSectionEBBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open Business Intelligence Development Studio.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">On the <b>File</b> menu, point to <b>Open</b>, and then click <b>Project/Solution</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Locate the CalculatedColumns Sample folder, and then double-click the file named CalculatedColumns.sln.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In <b>Solution Explorer</b>, right-click CalculatedColumns.dtsx in the <b>SSIS Packages</b> folder, and then click <b>Execute Package</b>. </p>
            </content>
          </li></ol></div>
    </content></div><h1 class="heading">Components in Sample</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following table lists the tasks, containers, data sources and destinations, and transformations that are used within the sample.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              Element
            </th>
            <th>
              Purpose
            </th>
          </tr><tr>
          <td>
            <p>Data Flow task</p>
          </td>
          <td>
            <p>The Data Flow task, <b>Calculate Values</b>, executes the data flow in the package.</p>
          </td>
        </tr><tr>
          <td>
            <p>File System task</p>
          </td>
          <td>
            <p>The File System task, <b>Copy File</b>, copies results.txt from the installation folder in Program Files, a folder to which users typically do not have write access, to the user's Temp folder.</p>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB source </p>
          </td>
          <td>
            <p>The first component within the data flow is an OLE DB source named <b>Extract Data</b>. This loads the archived sales transaction source data from the <b>TransactionHistoryArchive</b> table in the <b>AdventureWorks</b> database.</p>
          </td>
        </tr><tr>
          <td>
            <p>Derived Column transformation</p>
          </td>
          <td>
            <p>The first Derived Column transformation is named <b>Calculate LineItemTotalCost</b>. This transformation creates a new column, <b>LineItemTotalCost</b>, by multiplying the quantity by the cost for each sales transaction and storing it in the new column. This new column is then added to each output row.</p>
          </td>
        </tr><tr>
          <td>
            <p>Aggregate transformation</p>
          </td>
          <td>
            <p>The Aggregate transformation, <b>Sum Quantity and LineItemTotalCost</b>, groups the data by the <b>ProductID</b> column, and for each <b>ProductID</b>, calculates the sum of its <b>Quantity</b> column as the <b>QuantitySum</b> column, and calculates the sum of the <b>LineItemTotalCost</b> column as the <b>TotalCostByID</b> column.</p>
          </td>
        </tr><tr>
          <td>
            <p>Derived Column transformation</p>
          </td>
          <td>
            <p>The second Derived Column transformation, <b>Calculate Average Cost</b>, adds a new column, <b>AvgCostByID</b>, to each output row. The column contains the <b>TotalCostByID</b> divided by the <b>QuantitySum</b> for each ProductID.</p>
          </td>
        </tr><tr>
          <td>
            <p>Sort transformation</p>
          </td>
          <td>
            <p>The Sort transformation, <b>Sort by ProductID</b>, sorts the results by the <b>ProductID</b> column.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File destination</p>
          </td>
          <td>
            <p>The Flat File destination, <b>Load Data</b>, saves the data to the flat file, results.txt.</p>
          </td>
        </tr><tr>
          <td>
            <p>File connection manager</p>
          </td>
          <td>
            <p>The three File connection managers connect to the source, the copy of the result.txt file, and the Temp directory.</p>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB connection manager</p>
          </td>
          <td>
            <p>The OLE DB connection manager, <b>(local).AdventureWorks</b>, connects to the <b>AdventureWorks</b> database on the local server.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Sample Results</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The execution results of the Calculated Columns sample package is saved to a text file located in the <b>temp</b> directory. To locate the <b>temp</b> directory on your computer, click <b>Start</b>, click<b> Run</b>, type <b>%temp%</b>, and then click <b>OK</b>. This will open your <b>temp</b> folder. In this folder, find and open results.txt.<code> </code>The contents of this file should match the contents of the results.txt file found in the following folder:</p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns.</p>
      <p xmlns="">This pre-populated results.txt file is included for you to print or view before running the package.</p>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>